package ch.jhc.db;

import java.sql.SQLException;
import java.util.List;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import ch.jhc.Global;
import ch.jhc.bean.Article;

/*CRUD(Create,Retrieve,Update,Delete)*/

public class ArticleDao {
	//添加一篇文章add    设计参数runner  直接传入所有的参数
	
	public static void add(QueryRunner runner,Article article) throws SQLException {
		final String sql = "insert into article(title,content,publishDate,userId)"
				+ "values(?,?,?,?)";
			runner.execute(sql, article.getTitle(),article.getContent(),
					article.getPublishDate(),article.getUserId());
	}
	
	//给这个id 删除文章
	public static void del(QueryRunner runner,int id) throws SQLException {
		final String sql = "delete from article where id=?";
		runner.execute(sql, id);
	}
	
	public static long count(QueryRunner runner) throws SQLException {
		final String sql = "select count(id) from article";
		return runner.query(sql, new ScalarHandler<Long>());
	}
	
	//提供id 来更新
	public static void update(QueryRunner runner,Article newArticle) {
		
	}
	
	//查询所有文章 的任务      分页   第几页的数据查询出来
	public static List<Article> find(QueryRunner runner,int pageNum) throws SQLException {
		final String sql = "select article.id,title,content,publishDate,userId,username from article "
				+ "left join user on article.userId = user.id "
				+ "order by publishDate desc limit ? offset ?";//根据日期来降序排    每一行自从拆成一个对象
		return runner.query(sql,new BeanListHandler<>(Article.class),
				Global.PAGE_SIZE,pageNum * Global.PAGE_SIZE);//pageNum * Global.PAGE_SIZE  从0开始
	}
	
	//只想找到一篇文章
	public static Article findOne(QueryRunner runner,int id) {
		return null;
	}
	public static long userTotal(QueryRunner runner,int creator) throws SQLException {
		final String sql = "select count(id) from article where userId = ?";
		return runner.query(sql, new ScalarHandler<Long>(),creator);
	}
	
	public static List<Article> userList(QueryRunner runner,int pageNum,int creator) throws SQLException {
		final String sql = "select article.id, title, content, userId, publishDate from user,article "
				+ "where user.id = article.userId and userId = ? "
				+ "order by publishDate desc limit ? offset ?";
		return runner.query(sql, new BeanListHandler<>(Article.class),creator,30,(pageNum-1) * 30);
	}

}
